package net.sf.rose.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.rose.exception.SystemErrorCode;
import net.sf.rose.exception.SystemException;
import net.sf.rose.jdbc.bean.FieldBean;
import net.sf.rose.jdbc.factory.BasicConnectFactory;
import net.sf.rose.jdbc.service.DataBaseFactory;
import net.sf.rose.jdbc.service.Service;

import org.apache.log4j.Logger;

/**
 * 功能：数据库基础设置
 * @author fengjian
 * @since 2014/06/24
 */
public class DBUtils {

	private static Logger logger = Logger.getLogger(DBUtils.class);

	/** 当前使用的数据库类型 */
	private static int databaseType = 1;
	public static int DATABASE_TYPE_MYSQL = 1;// mysql数据库类型
	public static int DATABASE_TYPE_ORACLE = 2;// oracle数据库类型

	/** 定义数据库表操作标记 */
	public static final String TYPE_INSERT = "insert"; // 插入
	public static final String TYPE_UPDATE = "update"; // 更新
	public static final String TYPE_SAVE = "save"; // 保存
	public static final String TYPE_DELETE = "delete"; // 删除
	public static final String TYPE_LOAD = "load"; // 单个数据
	public static final String TYPE_LIST = "list"; // 列表
	public static final String TYPE_PAGE = "page"; // 分页
	public static final String TYPE_MAP = "map"; // Map键值对

	/**
	 * 返回当前使用的数据库类型
	 */
	public static int getDatabaseType() {
		return databaseType;
	}

	/**
	 * 设置当前使用的数据库类型
	 */
	public static void setDatabaseType(int databaseType) {
		DBUtils.databaseType = databaseType;
	}

	/**
	 * 判断当前数据库是否是Mysql
	 */
	public static boolean isMysqlDatabaseUsed() {
		return databaseType ==DATABASE_TYPE_MYSQL;
	}

	/**
	 * 判断当前数据库是否是Oracle
	 */
	public static boolean isOracleDatabaseUsed() {
		return databaseType ==DATABASE_TYPE_ORACLE;
	}

	/** 当前使用的数据库操作日志记录类 */
	private static Class<?> logClass = GenericLog4j.class;

	/**
	 * 返回当前使用的数据库操作日志记录类
	 */
	public static GenericLog getLogClassInstance() {
		try {
			return (GenericLog) logClass.newInstance();
		} catch (Exception e) {
			String message = "日志记录类初始化创建失败！";
			SystemException ex = new SystemException(message, SystemErrorCode.DAO_ERROR);
			throw ex;
		}
	}

	/**
	 * 设置当前使用的数据库操作日志记录类
	 */
	public static void setLogClass(Class<?> logClass) {
		DBUtils.logClass = logClass;
	}

	/** 数据库类型表主键定义缓存 */
	private static Map<String, String> tablePKMap = new HashMap<String, String>();

	/** 数据库类型表字段定义缓存 */
	private static Map<String, Map<String, FieldBean>> tableFieldsMap = new HashMap<String, Map<String, FieldBean>>();

	static {
		String sql = null;
		Connection con = null;
		try {
			con = DataBaseFactory.getService().getConnection();
			// 获取数据库表和视图信息
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet results = dbmd.getTables(null, null, "%", new String[] { "TABLE", "VIEW" });
			List<String> list = new ArrayList<String>();
			while (results.next()) {
				list.add(results.getString(3));
			}
			results.close();
			// 获取表主键信息
			for (String table : list) {
				results = dbmd.getPrimaryKeys(null, null, table);
				if (results.next()) {
					tablePKMap.put(results.getString(3), results.getString(4));
				}
			}
			// 获取表的字段信息
			PreparedStatement pstmt = null;
			for (String tableName : tablePKMap.keySet()) {
				sql = "SELECT * FROM " + tableName;
				Map<String, FieldBean> map = new HashMap<String, FieldBean>();
				pstmt = con.prepareStatement(sql);
				ResultSetMetaData metaData = pstmt.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					FieldBean field = new FieldBean();
					field.setTable(tableName);
					field.setField(metaData.getColumnName(i + 1));
					field.setJavaType(metaData.getColumnClassName(i + 1));
					map.put(field.getField(), field);
				}
				tableFieldsMap.put(tableName, map);
			}
		} catch (SQLException e) {
			String message = "数据库表元数据查找失败！";
			logger.error("sql:" + sql);
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.DAO_SEARCH_ERROR);
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				String message = "数据库连接断开失败！";
				logger.error(message, e);
			}
		}
	}

	/**
	 * 根据数据库表名取得数据库表主键名称
	 */
	public static String getKeyFieldName(String tableName) {
		String key = tablePKMap.get(tableName);
		if (key == null) {
			getDatabaseInfo(tableName);
			key = tablePKMap.get(tableName);
		}
		return key;
	}

	/**
	 * 根据数据库表名取得数据库表字段定义
	 */
	public static Map<String, FieldBean> getFields(String tableName) {
		Map<String, FieldBean> map = tableFieldsMap.get(tableName);
		if (map == null) {
			getDatabaseInfo(tableName);
			map = tableFieldsMap.get(tableName);
		}
		return map;
	}
	
	private static void getDatabaseInfo(String tableName) {
		String sql = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DataBaseFactory.getService().getConnection();
			// 获取表主键信息
			DatabaseMetaData dbmd = con.getMetaData();
			ResultSet results = dbmd.getPrimaryKeys(null, null, tableName);
			if (results.next()) {
				tablePKMap.put(results.getString(3), results.getString(4));
			}
			// 获取表的字段信息
			sql = "SELECT * FROM " + tableName;
			Map<String, FieldBean> map = new HashMap<String, FieldBean>();
			pstmt = con.prepareStatement(sql);
			ResultSetMetaData metaData = pstmt.getMetaData();
			for (int i = 0; i < metaData.getColumnCount(); i++) {
				FieldBean field = new FieldBean();
				field.setTable(tableName);
				field.setField(metaData.getColumnName(i + 1));
				field.setJavaType(metaData.getColumnClassName(i + 1));
				map.put(field.getField(), field);
			}
			tableFieldsMap.put(tableName, map);
		} catch (SQLException e) {
			String message = "数据库表元数据查找失败！";
			logger.error("sql:" + sql);
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.DAO_SEARCH_ERROR);
		} finally {
			try {
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				String message = "数据库连接断开失败！";
				logger.error(message, e);
			}
		}
	}

	/** 数据库查询缓存数据 */
	private static Map<String, Object> data = new HashMap<String, Object>();

	/**
	 * 添加数据库查询缓存数据
	 */
	public static void putCache(String key, Object val) {
		data.put(key, val);
	}

	/**
	 * 获取数据库查询缓存数据
	 */
	public static Object getCache(String key) {
		return data.get(key);
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.1.5)(PORT=1521))(ADDRESS =(PROTOCOL=TCP)(HOST=172.16.1.6)(PORT =1521))(FAILOVER =on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ntdb)(FAILOVER_MODE=(TYPE=SELECT))))";
		Class.forName("oracle.jdbc.driver.OracleDriver");
		BasicConnectFactory factory = new BasicConnectFactory();
		factory.setUrl(url);
		factory.setUser("port_ship");
		factory.setPassword("123456");
		Service s = new Service();
		s.setConnectFactory(factory);
		Map<String, FieldBean> map = DBUtils.getFields("view_ship_supply");
		s.close();
		System.out.println(map);
	}
}
